#!/bin/bash
#设置任务异常退出
set -eo pipefail
dt='{{ execution_date | cst_ds }}'
name='T-1出港转运及时率-汇总兔优达'
# 在退出时清理文件
trap "rm -f /tmp/${name}_${dt}.zip /tmp/${name}_${dt}.csv && echo 'removed tmp files'" EXIT
# 创建txt文件
touch /tmp/${name}_${dt}.txt
#将中文字段名输入文件顶部
#将查询结果追加到文件中
{
  echo -e "始发地发件中心编码\t始发地发件中心名称\t订单来源编码\t订单来源名称\t中心类型1始发2中转\t发件代理区编码\t发件代理区名称\t发件票数\t静态路由缺失票数\t转运及时票数\t出港转运及时率\t无到达时间票数\t错发票数\t操作延误票数\t发车晚点票数\t任务单问题票数\t经停点问题票数\t全链路转运及时票数\t全链路转运及时率\t任务单错用票数\t管理大区编码\t管理大区名称\t日期分区\t">/tmp/${name}_${dt}.txt;
  spark-sql --driver-memory 8G --num-executors 10 --executor-memory 12g --executor-cores 4  --conf spark.executor.memoryOverhead=4g --conf spark.driver.maxResultSize=3g -S -e "
with departure_transfer as (
    select billcode
         , start_code
         , start_name
         , order_source_code
         , order_source_name
         , start_agent_code
         , start_agent_name
         , center_type
         , nextstationcode
         , nextstation
         , send_scantime
         , send_shipment_no
         , planned_departure_time
         , actual_departure_time
         , next_planned_arrival_time
         , packagecode
         , goods_type_code
         , goods_type_name
         , arrival_shipment_no
         , arrival_time
         , all_line_arrival_time
         , last_network_code
         , last_network_name
         , transfer_hours
         , route_send_time
         , all_line_route_send_time
         , is_timely
         , untimely_cause
         , all_line_is_timely
         , all_line_untimely_cause
         , route_lack
         , route_error
         , end_code
         , end_name
         , r_nxt_arrival_time
         , all_line_r_nxt_arrival_time
         , r_lastes_arrival_time
         , all_line_r_lastes_arrival_time
         , stop_type
         , pack_send_time
         , mage_region_code
         , mage_region_name
         , business_attribute
         , r_next_lastes_arrival_time
         , all_r_next_lastes_arrival_time
         , arrival_actual_arrival_time
         , arrival_planned_arrival_time
         , reserved_seconds
         , sign_agent_code
         , sign_agent_name
         , end_center_code
         , end_center_name
         , center_arrvial_time
         , replace(terminal_dispatch_code, ',', '_') as terminal_dispatch_code
         , shipment_arrival_time
         , dt
    from jms_dm.dm_cn_departure_transfer_timely_route_dt
    where dt  = '{{ execution_date | cst_ds }}'
),
     waybill_incre as (
         select waybill_no
         from jms_dwd.dwd_yl_oms_oms_waybill_incre_dt
         where dt >= date_add('{{ execution_date | cst_ds }}', -15)
           and dt <= '{{ execution_date | cst_ds }}'
           and express_type_name = '兔优达'
     )
select nvl(start_code, null)                                                                as start_code,
       nvl(max(start_name), null)                                                           as start_name,
       nvl(max(order_source_code), null)                                                    as order_source_code,
       nvl(order_source_name, null)                                                         as order_source_name,
       nvl(center_type, null)                                                               as center_type,
       nvl(max(start_agent_code), null)                                                     as start_agent_code,
       nvl(max(start_agent_name), null)                                                     as start_agent_name,
       sum(1)                                                                               as send_account,
       sum(if(is_timely = 2, 1, 0))                                                         as route_lack_account,
       sum(if(is_timely = 1, 1, 0))                                                         as transfer_intime_account,
       sum(if(is_timely = 1, 1, 0)) / (sum(1) - sum(if(is_timely = 2, 1, 0)) -
                                       sum(if(untimely_cause = 2, 1, 0)))                   as transfer_intime_rate,
       sum(if(untimely_cause = 7, 1, 0))                                                    as without_arrival_account,
       sum(if(untimely_cause = 2, 1, 0))                                                    as error_send_account,
       sum(if(untimely_cause = 5, 1, 0))                                                    as opp_delay_account,
       sum(if(untimely_cause = 4, 1, 0))                                                    as send_delay_account,
       sum(if(untimely_cause = 3, 1, 0))                                                    as shipment_delay_account,
       sum(if(untimely_cause = 6, 1, 0))                                                    as stop_delay_account,
       sum(if(all_line_is_timely = 1, 1, 0))                                                as all_line_transfer_intime_account,
       sum(if(all_line_is_timely = 1, 1, 0)) / (sum(1) - sum(if(all_line_is_timely = 2, 1, 0)) -
                                                sum(if(all_line_untimely_cause = 2, 1, 0))) as all_line_transfer_intime_rate,
       sum(if(untimely_cause = 9, 1, 0))                                                    as shipment_error_account,
       nvl(mage_region_code, null)                                                          as mage_region_code,
       nvl(max(mage_region_name), null)                                                     as mage_region_name,
       dt
from departure_transfer t1
         join waybill_incre t2
              on t1.billcode = t2.waybill_no
group by start_code
       , order_source_name
       , dt
       , center_type
       , mage_region_code
;
 " >>/tmp/${name}_${dt}.txt
}

# 替换分隔符，并输出csv
cat /tmp/${name}_${dt}.txt | tr -s '\t' ',' > /tmp/${name}_${dt}.csv
# 压缩并删除 csv 文件
zip -jrm /tmp/${name}_${dt}.zip /tmp/${name}_${dt}.csv
# 上传文件
/usr/local/python3/bin/python3  /usr/local/airflow/dags/utils/operators/upload_oss.py ${name}/${name}_${dt}.zip /tmp/${name}_${dt}.zip
# zip 文件将在进程退出后被自动清理

#声明文件大小
data_size=$(ls -lh /tmp/${name}_${dt}.zip | awk '{print $5}')
#打印文件信息
Message="${name}_${dt}.zip 上传成功 大小: $data_size"
#配置钉钉告警
curl 'https://oapi.dingtalk.com/robot/send?access_token=816a8f35fd84432ffa6bff9d7714abc322628bb4a8ded86908550d2335aba613' \
   -H 'Content-Type: application/json' \
   -d "
  {\"msgtype\": \"text\",
    \"text\": {
        \"content\": \"监控内容:$Message\"
     }
  }" || true
rm -f  /tmp/${name}_${dt}.txt